package com.xingxuecn.limit.dao.impl;

import com.xingxuecn.limit.common.model.Page;
import com.xingxuecn.limit.common.model.User;
import com.xingxuecn.limit.common.utils.DbUtils;
import com.xingxuecn.limit.dao.UserDao;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

/**
 * @author 陈前钱 at 2018/9/10 0010 下午 3:42
 */
public class UserDaoImpl implements UserDao {
    private QueryRunner qr = new QueryRunner();

    @Override
    public List<User> findAll() throws SQLException {
        String sql = "select * from user";
        Connection connection = DbUtils.getConnection();
        List<User> result;
        try {
            result = qr.query(connection, sql, new BeanListHandler<User>(User.class));
        } finally {
            connection.close();
        }
        return result;
    }

    @Override
    public List<User> search(User user, Integer... limit) throws SQLException {
        StringBuffer sql = new StringBuffer("select * from user where 1=1");
        List<Object> jdbcParams = new LinkedList<>();
        if (user.getId() != null) {
            sql.append(" and id = ?");
            jdbcParams.add(user.getId());
        }
        if (user.getName() != null) {
            sql.append(" and name like ?");
            jdbcParams.add("%" + user.getName() + "%");
        }

        if (limit != null && limit.length > 0) {
            if (limit.length == 1) {
                sql.append(" limit ?");
                jdbcParams.add(limit[0]);
            } else if (limit.length == 2) {
                sql.append(" limit ?,?");
                jdbcParams.add(limit[0]);
                jdbcParams.add(limit[1]);
            } else {
                throw new RuntimeException("参数传多了，limit最大只能2个");
            }
        }
        Connection connection = DbUtils.getConnection();
        List<User> result;
        try {
            result = qr.query(connection, sql.toString(), new BeanListHandler<User>(User.class), jdbcParams.toArray());
        } finally {
            connection.close();
        }
        return result;
    }

    @Override
    public int add(User user) throws SQLException {
        String sql = "insert into user(id,name,url) values(?,?,?)";
        BigInteger id = null;
        Connection connection = DbUtils.getConnection();
        try {
            id = qr.insert(connection, sql, new ScalarHandler<BigInteger>(), new Object[]{
                    user.getId(),
                    user.getName(),
                    user.getUrl(),

            });
        } finally {
            connection.close();
        }
        if (id != null) {
            user.setId(id.intValue());
        }

        return 1;
    }

    @Override
    public int update(User user) throws SQLException {
        String sql = "update user set name =?,url=? where id = ?";
        int rows;
        Connection connection = DbUtils.getConnection();
        try {
            rows = qr.update(connection, sql, new Object[]{
                    user.getName(),
                    user.getUrl(),

                    user.getId()
            });
        } finally {
            connection.close();
        }
        return rows;
    }

    @Override
    public int delete(Integer id) throws SQLException {
        String sql = "delete from user where id = ?";
        int rows;
        Connection connection = DbUtils.getConnection();
        try {
            rows = qr.execute(connection, sql, new Object[]{id});
        } finally {
            connection.close();
        }
        return rows;
    }


    @Override
    public int count(User user) throws SQLException {
        StringBuffer sql = new StringBuffer("select count(*) from user where 1=1");
        List<Object> jdbcParams = new LinkedList<>();
        if (user.getId() != null) {
            sql.append(" and id = ?");
            jdbcParams.add(user.getId());
        }
        if (user.getName() != null) {
            sql.append(" and name like ?");
            jdbcParams.add("%" + user.getName() + "%");
        }

        Connection connection = DbUtils.getConnection();
        Long result;
        try {
            result = qr.query(connection, sql.toString(), new ScalarHandler<Long>(), jdbcParams.toArray());
        } finally {
            connection.close();
        }
        if (result != null) {
            return result.intValue();
        }
        return 0;
    }

    @Override
    public Page searchPage(User user, Integer page, Integer rows) throws SQLException {
        int total = this.count(user);
        List<User> menuList = this.search(user, (page - 1) * rows, rows);
        return new Page(total,menuList);
    }
}
